<?php

/*

	Copyright © 2011 Mike Bennett <mike@kereru.com>

	This file is part of Tazu-Time.

    Tazu-Time is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    any later version.

    Tazu-Time is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with Tazu-Time.  If not, see <http://www.gnu.org/licenses/>.
    
*/



//$e = get_loaded_extensions();
//array_dump($e);

class Timesheet extends Db {

	var $customers = FALSE;			// customers.db

	// Reports
//	var $reports = array();
//	var $report_id='';
//	var $customer_id='';
//	var $task_id='';
//	var $start='';
//	var $end='';
	
	// Report writing strings
//	var $name = '';					// Company name for reports

//	var $output_file = FALSE;
	
	
	function __construct($data_file=FALSE, $customers=FALSE) {

		parent::__construct($data_file);
		$this->customers = $customers;
		
/*
		$this->reports = array(
		
								1 => array(
										'code'		=>	'daily',
										'name'		=> 'Customer(s): All projects, sub-totals by day.',
										'customer'	=> FALSE,
										'task'		=> FALSE,
										'html'		=> TRUE,
									),
								
								2 => array(
										'code'		=>	'daily_proj',
										'name'		=> 'Project: Selected project, sub-totals by day.',
										'customer'	=> FALSE,
										'task'		=> TRUE,
										'html'		=> TRUE,
									),
								
								3 => array(
										'code'		=>	'project_list',
										'name'		=> 'Projects: All project codes and descriptions.',
										'customer'	=> FALSE,
										'task'		=> FALSE,
										'html'		=> TRUE,
									),
								
								4 => array(
										'code'		=>	'billing',
										'name'		=> 'Billing: Hours and charging for selected period. (Without fixed charges)',
										'customer'	=> FALSE,
										'task'		=> FALSE,
										'html'		=> TRUE,
									),
								
								5 => array(
										'code'		=>	'billing_all',
										'name'		=> 'Billing: Hours and charging for selected period. (All)',
										'customer'	=> FALSE,
										'task'		=> FALSE,
										'html'		=> TRUE,
									),

								6 => array(
										'code'		=>	'timesheets_csv',
										'name'		=> 'CSV Timesheets: Timesheet data as a CSV file.',
										'customer'	=> FALSE,
										'task'		=> FALSE,
										'csv'		=> TRUE,
									),
								
								);
	
*/

	}

	
	/* Check database structure */
	function database_check() {
		global $gl;

		if ( ! file_exists( $this->db_path.$this->db_file.$this->db_ext ) ) {

//wb_message_box(null,$this->dsn);

			$db_ver = '0.9';
			
			
			try {
				
				$db = new PDO($this->dsn);
	
				// status
				$qry = "CREATE TABLE status(
					id INTEGER PRIMARY KEY AUTOINCREMENT,
				    param  TEXT,
				    value TEXT
				)";
								
				$db->exec($qry);
	
	
				// customers
				$qry = "CREATE TABLE customers(
					id INTEGER PRIMARY KEY AUTOINCREMENT,
				    name  TEXT,
				    company TEXT
				)";
								
				$db->exec($qry);
	
	
				// entries
				$qry = "CREATE TABLE entries(
					id INTEGER PRIMARY KEY AUTOINCREMENT,
				    customer_id  INTEGER,
				    task_id TEXT,
				    start  TEXT,
				    elapsed INTEGER,
				    comment BLOB
				)";
								
				$db->exec($qry);
	
	
				// tasks
				$qry = "CREATE TABLE tasks(
					id INTEGER PRIMARY KEY AUTOINCREMENT,
				    customer_id  INTEGER,
				    task_code  TEXT,
				    task TEXT
				)";
								
				$db->exec($qry);
	
	
				$qry = sprintf("INSERT INTO status (param,value) VALUES ('db_ver','%s');",$db_ver);
				$db->exec($qry);


				$db = null;
				
			} catch (PDOException $e) {
			   $this->error($e->getMessage());
				return;
			}

		}
		


		// Current database version

		$this->db_ver = $this->getDBVersion();

		if ( $this->db_ver == 0.9 ) {

			// Upgrade 0.9 -> 1.0
			$db_ver = '1.0';

			try {
			
				$db = new PDO($this->dsn);
	
				$qry = "ALTER TABLE customers ADD email TEXT;";
				$db->exec($qry);

				$qry = "ALTER TABLE tasks ADD COLUMN estimate_time INTEGER DEFAULT NULL";
				$db->exec($qry);

				$qry = "ALTER TABLE tasks ADD COLUMN estimate_warning INTEGER DEFAULT NULL";
				$db->exec($qry);

				$qry = sprintf("UPDATE status set value='%s' where param='db_ver'",$db_ver);
				$db->exec($qry);

				$this->db_ver = $db_ver;
	
				$db = null;
				
	
			} catch (PDOException $e) {
			   $this->error($e->getMessage());
				return;
			}


		}

		$this->db_ver = $this->getDBVersion();
		if ( $this->db_ver == 1.0 ) {

			// Upgrade 1.0 -> 1.1
			$db_ver = '1.1';

			try {
			
				$db = new PDO($this->dsn);
	
				$qry = "ALTER TABLE tasks ADD COLUMN charged INTEGER DEFAULT 0";
				$db->exec($qry);

				$qry = "ALTER TABLE tasks ADD COLUMN period INTEGER DEFAULT 0"; // 0=hour 1=billing period
				$db->exec($qry);

				$qry = "ALTER TABLE tasks ADD COLUMN rate FLOAT DEFAULT 0";
				$db->exec($qry);

				$qry = sprintf("UPDATE status set value='%s' where param='db_ver'",$db_ver);
				$db->exec($qry);

				$this->db_ver = $db_ver;
	
				$db = null;
				
	
			} catch (PDOException $e) {
			   $this->error($e->getMessage());
				return;
			}


		}



		$this->db_ver = $this->getDBVersion();
		if ( $this->db_ver == 1.1 ) {

			// Upgrade 1.1 -> 1.2
			$db_ver = '1.2';

			try {
			
				$db = new PDO($this->dsn);

				// No more deleting entries, just flag as deleted	
				$qry = "ALTER TABLE entries ADD COLUMN deleted INTEGER DEFAULT 0";
				$db->exec($qry);

				$qry = sprintf("INSERT INTO status (param) values ('worker');",$db_ver);
				$db->exec($qry);

				$qry = sprintf("UPDATE status set value='%s' where param='db_ver'",$db_ver);
				$db->exec($qry);

				$this->db_ver = $db_ver;
	
				$db = null;
				
	
			} catch (PDOException $e) {
			   $this->error($e->getMessage());
				return;
			}


		}



		$this->db_ver = $this->getDBVersion();
		if ( $this->db_ver == 1.2 ) {

			// Upgrade 1.2 -> 1.3
			$db_ver = '1.3';

			try {
			
				$db = new PDO($this->dsn);

				// Add mileage.
				$qry = "ALTER TABLE entries ADD COLUMN distance FLOAT DEFAULT 0";
				$db->exec($qry);

				// Phone calls
				$qry = "ALTER TABLE entries ADD COLUMN phone_num TEXT";
				$db->exec($qry);

				$qry = sprintf("UPDATE status set value='%s' where param='db_ver'",$db_ver);
				$db->exec($qry);

				$this->db_ver = $db_ver;
	
				$db = null;
				
	
			} catch (PDOException $e) {
			   $this->error($e->getMessage());
				return;
			}


		}


		$this->db_ver = $this->getDBVersion();
		if ( $this->db_ver == 1.3 ) {

			// Upgrade 1.3 -> 1.4
			$db_ver = '1.4';

			try {
			
				$db = new PDO($this->dsn);


				// Extra data stored against sessions
//			    entry_id TEXT,							// link to entries.id
//			    type INTEGER,							// link to dat_types.id
//			    value TEXT,								// data 
//			    comment TEXT							// comment

				$qry = "CREATE TABLE entry_dat(
					id INTEGER PRIMARY KEY AUTOINCREMENT,
				    entry_id  INTEGER,							
				    type INTEGER,
				    value TEXT,	
				    comment TEXT
				)";
				$db->exec($qry);

				$qry = sprintf("UPDATE status set value='%s' where param='db_ver'",$db_ver);
				$db->exec($qry);

				$this->db_ver = $db_ver;
	
				$db = null;
				
	
			} catch (PDOException $e) {
			   $this->error($e->getMessage());
				return;
			}


		}


if ( TRUE ) {

		$this->db_ver = $this->getDBVersion();
		if ( $this->db_ver == 1.4 ) {

			// Upgrade 1.4 -> 1.5
			// Splits customers and tasks out into a separate database file in user settings area
			$db_ver = '1.5';

			@copy( $this->filename , $this->filename.'.bak' );

			if ( ! file_exists( $this->cust_filename ) ) {

				// Take a copy of current timesheets file
				if ( @copy( $this->filename , $this->cust_filename ) ) {

					// Remove unwanted tables
					try {
			
						$db = new PDO($this->cust_dsn);

						$qry = sprintf("DROP TABLE entries;");
						$db->exec($qry);

						$qry = sprintf("DROP TABLE entry_dat;");
						$db->exec($qry);

						$qry = sprintf("DELETE FROM status WHERE param <> 'db_ver';");
						$db->exec($qry);

						$qry = sprintf("VACUUM;");

						$db->exec($qry);


					} catch (PDOException $e) {
					   $this->error($e->getMessage());
						return;
					}
					
									
				}
			
			}

			// Remove unwanted tables from timesheets database
			try {
			
				$db = new PDO($this->dsn);

				$qry = sprintf("DROP TABLE customers;");
				$db->exec($qry);

				$qry = sprintf("DROP TABLE tasks;");
				$db->exec($qry);

				$qry = sprintf("VACUUM;");
				$db->exec($qry);

				$qry = sprintf("UPDATE status set value='%s' where param='db_ver'",$db_ver);
				$db->exec($qry);

				$this->db_ver = $db_ver;
	
				$db = null;


			} catch (PDOException $e) {
			   $this->error($e->getMessage());
				return;
			}
					
			

		}
			
			

}


	}
	

	/**
	*	Determine if this is a managed timesheet
	*	
	*	@param	
	*	@return	
	*	
	*/
	function isManaged() {
	
		if ( $this->getWorker() > 0 ) {
			return( TRUE );
		}
	
		return(FALSE);

	}


	// --- text Translations
	
	function getTextTranslations() {
	
		$qry = sprintf("SELECT * FROM xlate;",$entry_id);
		$result = array();
		$rows = $this->rows($qry,array('id','code','customer','default_txt','replace'));

		$result = array();
		if ( $rows ) {
			foreach ( $rows as $idx => $row ) {
				if ( trim($row['replace']) == '' ) {
					// Default
					$result[strtolower($row['code'])] = trim($row['default_txt']);
				}
				else {
					$result[strtolower($row['code'])] = trim($row['replace']);
				}
			}
		}

		return( $result );

	}


	// --- Session data
	
	function delDat($data_id) {
		$qry = sprintf("DELETE FROM entry_dat WHERE id='%s';",$data_id);
		return( $this->execute($qry) );
	}

	function getData($entry_id) {
		$qry = sprintf("SELECT * FROM entry_dat WHERE entry_id='%s';",$entry_id);
		$rows = $this->rows($qry,array('id','entry_id','task_id','type','value','comment'));
		if ( ! $rows ) $rows = array();	
		return( $rows );
	}

	function getDatType($id) {
		return( $this->col($id,'entry_dat','type') );
	}

	function getDatValue($id) {
		return( $this->col($id,'entry_dat','value') );
	}

	function getDatComment($id) {
		return( $this->col($id,'entry_dat','comment') );
	}

	function updateDat($id,$col,$val) {
		$qry = sprintf("UPDATE entry_dat SET %s='%s' WHERE id='%s';",$col,$val,$id);

		return( $this->execute($qry));		
	}

	function adddat($entry_id, $type,$value,$description='') {
		$qry = sprintf("INSERT INTO entry_dat (entry_id,type,value,comment) VALUES ('%s','%s','%s','%s');",
						$entry_id,$type,$value,$description);
		return( $this->insert($qry) );
	}


	function getProjects($customer_id) {
	
		$cs = new Customers($this->customers);
		$projects = $cs->getProjectList($customer_id);	

//print_r($projects);
	
		$result = array();

		// Massage for display
		foreach ( $projects as $i => $dat ) {

			$result[$i] = $dat;
			
			$result[$i][3] = $this->elapsed($dat['estimate_time']);
			if ( $result[$i][3] == '00:00' ) $result[$i][3] = '';
			$result[$i]['estimate_time'] = 	$result[$i][3];
					
			$result[$i][4] = $this->getProjectElapsed($dat['id']);
			$result[$i]['elapsed'] = $result[$i][4];

		}
		
		return( $result );

	}



	// --- Timesheet entries ------------------------------------------------

	/**
	*	Set the 'worker' column value
	*	
	*	@param	
	*	@return	
	*	
	*/
	function setWorkerId($id) {

		$qry = sprintf("UPDATE status set value='%s' where param='worker'",$id);
		$this->execute($qry);

		return(TRUE);		
	}

	function getPreviousEntry($customer_id,$entry_id) {
		// Return the entry that is chronologically before this one

		$qry = sprintf("SELECT * FROM entries WHERE customer_id='%s' AND deleted=0 order by start desc;",$customer_id);

		$result = FALSE;
		$rows = $this->rows($qry,array('id','task_id','start','elapsed'));

		$found = FALSE;

		if ( $rows ) {
			foreach ( $rows as $i => $dat ) {
	
				if ( $found ) {
					return( $dat[0] );
				}
	
				if ( $dat[0] == $entry_id ) {
					// Found our entry, next one is the one we want
					$found = TRUE;
				}
			}
		}
		return( FALSE );
	}

	function addEntry($customer_id,$task_id,$start,$elapsed,$comment) {
	
		// Elapsed is in seconds, round up to nearest minute
		$elapsed = $this->rnd_secs($elapsed);

		$comment = str_replace("'","''",$comment);
		
		$qry = sprintf("INSERT INTO entries (customer_id,task_id,start,elapsed,comment) VALUES ('%s','%s','%s','%s','%s');",$customer_id,$task_id,$start,$elapsed,$comment);

		return( $this->insert($qry) );
	}
	

	// Return list of all timesheet entries for a customer

	function getEntries($customer_id) {
		$qry = sprintf("SELECT * FROM entries WHERE customer_id='%s'  AND deleted=0  order by start desc;",$customer_id);
		$result = array();
		$rows = $this->rows($qry,array('id','task_id','start','elapsed'));

		$cs = new Customers($this->customers);


		// Massage for display
		if ( $rows ) {
			foreach ( $rows as $i => $dat ) {
	
				$result[$i] = $dat;
				$result[$i][1] = $cs->getProjectCode($dat[1]);
				$result[$i][2] = $this->day_time($this->timestamp2unix($dat[2]));
				$result[$i][3] = $this->elapsed($dat[3]);
			}
		}
		return( $result );

	}

	/**
	*	Delete all entries older than $months
	*	
	*	@param	
	*	@return	
	*	
	*/
	function purgeEntries($date) {
		$qry = sprintf("DELETE FROM entries WHERE start <= '%s'",$date);
		$this->execute($qry);

		$qry = sprintf("VACUUM;");
		
		return( $this->execute($qry));		
	}

	function getEntryTask($entry_id) {
		return( $this->col($entry_id,'entries','task_id') );
	}

	function getEntryComment($entry_id) {

	
		$comment = str_replace("''","'",$this->col($entry_id,'entries','comment'));
	
		return( $comment );
	}

	function getEntryStart($entry_id) {
		return( $this->col($entry_id,'entries','start') );
	}

	function getEntryEnd($entry_id) {
		$start = $this->col($entry_id,'entries','start');
		$start = $this->timestamp2unix($start);
		
		$elapsed = $this->col($entry_id,'entries','elapsed');
		$end = $start+$elapsed;
		
		$end = $this->unix2timestamp($end);
		
		return( $end );
	}

	function getEntryStartDay($entry_id) {
		$start = $this->col($entry_id,'entries','start');
		$day = substr($start,0,10);
		return( $day );
	}

	function getEntryElapsed($entry_id) {
		return( $this->col($entry_id,'entries','elapsed') );
	}

	function updateEntryComment($entry_id,$comment) {
		$comment = str_replace("'","''",$comment);
		$qry = sprintf("UPDATE entries SET comment='%s' WHERE id='%s';",$comment,$entry_id);
		return( $this->execute($qry));		
	}

	function updateEntryTask($entry_id,$task_id) {
		$qry = sprintf("UPDATE entries SET task_id='%s' WHERE id='%s';",$task_id,$entry_id);
		return( $this->execute($qry));		
	}

	function updateEntryStart($entry_id,$start) {
		$qry = sprintf("UPDATE entries SET start='%s' WHERE id='%s';",$start,$entry_id);
		return( $this->execute($qry));		
	}

	function updateEntryElapsed($entry_id,$elapsed) {
		$qry = sprintf("UPDATE entries SET elapsed='%s' WHERE id='%s';",$elapsed,$entry_id);
		return( $this->execute($qry));		
	}

	
	function delEntry($entry_id) {
//		$qry = sprintf("DELETE FROM entries WHERE id='%s'",$entry_id);
		$qry = sprintf("UPDATE entries set deleted=1 WHERE id='%s'",$entry_id);
		return( $this->execute($qry));		
	}


	function getProjectElapsed($id) {
		$elapsed = 0;
		$entries = array();

		$qry = sprintf("SELECT * FROM entries WHERE task_id='%s';",$id);
//echo $qry."\n";

//		$result = array();

		$rows = $this->rows($qry,array('id','elapsed'));

		// Massage for display
		if ( $rows ) {
			foreach ( $rows as $i => $dat ) {
	
				$entry_id = $dat['id'];
				$elapsed = $elapsed + $this->getEntryElapsed($entry_id);
			}
		}
		
		return( $this->elapsed($elapsed) );

	}



	function getWorker() {

		$ver = FALSE;
		
		try {
		
			
			$db = new PDO($this->dsn);

			$qry = "SELECT * FROM status WHERE param='worker' LIMIT 1;";

		   foreach ($db->query($qry, PDO::FETCH_ASSOC) as $row) {
				$ver = trim($row['value']);
		   }


			$db = null;
			

		} catch (PDOException $e) {
		   $this->error($e->getMessage());
		}

		return( $ver );

	}


}

?>